﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using UCNKompetenceBors.Model;

namespace UCNKompetenceBors.Database
{
    public class dbContactPerson
    {
        /// <summary>
        /// Gets a contact person, based on the contactPersonId parameter.
        /// </summary>
        /// <param name="contactPersonId">Specifies the ID of the contact person to be retrieved.</param>
        /// <returns>Returns a ContactPerson object.</returns>
        public static ContactPerson GetContactPersonById(int contactPersonId)
        {
            try
            {
                string connString = ConfigurationManager.ConnectionStrings["ConnStringDB"].ConnectionString;

                using (SqlConnection con = new SqlConnection(connString))
                {
                    string query = "select cp.contactPersonFName, cp.contactPersonLName, cp.contactPersonPhone, cp.contactPersonMail, cp.contactPersonTitle, cp.contactPersonDepartment, cp.contactPersonImageURL " +
                                   "from ContactPerson cp " +
                                   "where cp.contactPersonId = @ContactPersonId";
                    SqlCommand cmd = new SqlCommand(query, con);
                    cmd.CommandType = CommandType.Text;

                    cmd.Parameters.AddWithValue("@ContactPersonId", contactPersonId);

                    con.Open();

                    SqlDataReader dr = cmd.ExecuteReader();

                    ContactPerson cp = null;

                    while (dr.Read())
                    {
                        cp = new ContactPerson(contactPersonId);

                        cp.FirstName = dr["contactPersonFName"].ToString();
                        cp.LastName = dr["contactPersonLName"].ToString();
                        cp.Email = dr["contactPersonMail"].ToString();
                        cp.Title = dr["contactPersonTitle"].ToString();
                        cp.Department = dr["contactPersonDepartment"].ToString();
                        cp.ImageURL = dr["contactPersonImageURL"].ToString();

                        if (dr["contactPersonPhone"] != null)
                            cp.Phone = dr["contactPersonPhone"].ToString();
                    }

                    return cp;
                }
            }
            catch (SqlException e)
            {
                //insert logging here
                throw;
            }
            catch (Exception e)
            {
                //insert logging here
                throw;
            }
        }

        /// <summary>
        /// Gets a list of all contact persons.
        /// </summary>
        /// <returns>Returns a list of Contact Person objects.</returns>
        public static List<ContactPerson> GetAllContactPersons()
        {
            try
            {
                string connString = ConfigurationManager.ConnectionStrings["ConnStringDB"].ConnectionString;

                using (SqlConnection con = new SqlConnection(connString))
                {
                    List<ContactPerson> contactPersons = new List<ContactPerson>();

                    string query = "select cp.contactPersonId, cp.contactPersonFName, cp.contactPersonLName, cp.contactPersonPhone, cp.contactPersonMail, cp.contactPersonTitle, cp.contactPersonDepartment, cp.contactPersonImageURL " +
                                   "from ContactPerson cp " +
                                   "order by cp.contactPersonFName asc";

                    SqlCommand cmd = new SqlCommand();

                    cmd.CommandText = query;
                    cmd.Connection = con;
                    cmd.CommandType = CommandType.Text;

                    con.Open();

                    SqlDataReader dr = cmd.ExecuteReader();

                    while (dr.Read())
                    {
                        ContactPerson cp = new ContactPerson(Convert.ToInt32(dr["contactPersonId"].ToString()));

                        cp.FirstName = dr["contactPersonFName"].ToString();
                        cp.LastName = dr["contactPersonLName"].ToString();
                        cp.Email = dr["contactPersonMail"].ToString();
                        cp.Title = dr["contactPersonTitle"].ToString();
                        cp.Department = dr["contactPersonDepartment"].ToString();
                        cp.ImageURL = dr["contactPersonImageURL"].ToString();

                        if (dr["contactPersonPhone"] != null)
                            cp.Phone = dr["contactPersonPhone"].ToString();

                        contactPersons.Add(cp);
                    }

                    con.Close();
                    con.Dispose();

                    return contactPersons;
                }
            }
            catch (SqlException e)
            {
                //insert logging here
                throw;
            }
            catch (Exception e)
            {
                //insert logging here
                throw;
            }
        }

        /// <summary>
        /// Creates a contact person, based on the input parameters.
        /// </summary>
        /// <param name="fName">Specifies the first name to be used.</param>
        /// <param name="lName">Specifies the last name to be used.</param>
        /// <param name="hasImage">Specifies whether or not contact person has an image.</param>
        /// <param name="imageFileName">Specifies the file name of the image to be used.</param>
        /// <param name="phone">Specifies the phone to be used in the contact person.</param>
        /// <param name="email">Specifies the email to be used in the contact person.</param>
        /// <param name="title">Specifies the title to be used in the contact person.</param>
        /// <param name="department">Specifies the department to be used in the contact person</param>
        public static void CreateContactPerson(string fName, string lName, bool hasImage, string imageFileName, string phone, string email, string title, string department)
        {
            try
            {
                string connString = ConfigurationManager.ConnectionStrings["ConnStringDB"].ConnectionString;

                using (SqlConnection con = new SqlConnection(connString))
                {
                    string query = "insert into ContactPerson (contactPersonFName, contactPersonLName, contactPersonPhone, contactPersonMail, contactPersonTitle, contactPersonDepartment, contactPersonImageURL) " +
                                   "values (@cpFName, @cpLName, @cpPhone, @cpMail, @cpTitle, @cpDepartment, @cpImageURL)";

                    SqlCommand cmd = new SqlCommand(query, con);
                    cmd.CommandType = CommandType.Text;

                    cmd.Parameters.AddWithValue("@cpFName", fName);

                    cmd.Parameters.AddWithValue("@cpLName", lName);

                    cmd.Parameters.AddWithValue("@cpPhone", phone);

                    cmd.Parameters.AddWithValue("@cpMail", email);

                    cmd.Parameters.AddWithValue("@cpTitle", title);

                    cmd.Parameters.AddWithValue("@cpDepartment", department);

                    if (hasImage)
                    {
                        cmd.Parameters.AddWithValue("@cpImageURL", "~/Images/ContactPersons/" + fName.Replace(" ", "") + "_" + lName.Trim() + "/" + imageFileName);
                    }
                    else
                    {
                        cmd.Parameters.AddWithValue("@cpImageURL", "~/Images/ContactPersons/defaultCp.jpg");
                    }

                    con.Open();

                    cmd.ExecuteNonQuery();

                    con.Close();
                    con.Dispose();
                }
            }
            catch (SqlException e)
            {
                //insert logging here
                throw;
            }
            catch (Exception e)
            {
                //insert logging here
                throw;
            }
        }

        /// <summary>
        /// Deletes a contact person, based on the contactPersonId parameter.
        /// </summary>
        /// <param name="contactPersonId">Specifies the ID of the contact person to be deleted.</param>
        public static void DeleteContactPerson(int contactPersonId)
        {
            try
            {
                string connString = ConfigurationManager.ConnectionStrings["ConnStringDB"].ConnectionString;

                using (SqlConnection con = new SqlConnection(connString))
                {
                    string query = "delete from ContactPerson " +
                                   "where contactPersonId = @contactPersonId ";

                    SqlCommand cmd = new SqlCommand();

                    cmd.CommandText = query;
                    cmd.Connection = con;
                    cmd.CommandType = CommandType.Text;

                    cmd.Parameters.AddWithValue("@contactPersonId", contactPersonId);

                    con.Open();

                    cmd.ExecuteNonQuery();

                    con.Close();
                    con.Dispose();
                }
            }
            catch (SqlException e)
            {
                //insert logging here
                throw;
            }
            catch (Exception e)
            {
                //insert logging here
                throw;
            }
        }

        /// <summary>
        /// Checks whether or not a contact person has an image, based on the contactPersonId parameter.
        /// </summary>
        /// <param name="contactPersonId">Specifies the ID of the contact person to be used.</param>
        /// <returns>Returns a boolean, specifying whether or not a contact person has an image.</returns>
        public static bool ContactPersonHasImage(int contactPersonId)
        {
            try
            {
                bool result = false;
                string connString = ConfigurationManager.ConnectionStrings["ConnStringDB"].ConnectionString;

                using (SqlConnection con = new SqlConnection(connString))
                {
                    string query = "select cp.contactPersonImageURL " +
                                   "from ContactPerson cp " +
                                   "where cp.contactPersonId = @cpID";

                    SqlCommand cmd = new SqlCommand();

                    cmd.CommandText = query;
                    cmd.Connection = con;
                    cmd.CommandType = CommandType.Text;

                    cmd.Parameters.AddWithValue("@cpID", contactPersonId);

                    con.Open();

                    SqlDataReader dr = cmd.ExecuteReader();

                    while (dr.Read())
                    {
                        if (dr["contactPersonImageURL"].ToString() != "~/Images/ContactPersons/defaultCp.jpg")
                            result = true;
                    }

                    con.Close();
                    con.Dispose();

                    return result;
                }
            }
            catch (SqlException e)
            {
                //insert logging here
                throw;
            }
            catch (Exception e)
            {
                //insert logging here
                throw;
            }
        }

        /// <summary>
        /// Edits a contact person, based on the contactPersonId parameter.
        /// </summary>
        /// <param name="contactPersonId">Specifies the ID of the contact person to be used.</param>
        /// <param name="fName">Specifies the first name to be used.</param>
        /// <param name="lName">Specifies the last name to be used.</param>
        /// <param name="hasImage">Specifies whether or not news article has an image.</param>
        /// <param name="imagePath">Specifies the path of the current image.</param>
        /// <param name="imageFileName">Specifies the file name of the image to be used.</param>        
        /// <param name="phone">Specifies the phone to be used.</param>
        /// <param name="email">Specifies the email to be used.</param>
        /// <param name="title">Specifies the title to be used.</param>
        /// <param name="department">Specifies the department to be used</param>
        public static void EditContactPerson(int contactPersonId, string fName, string lName, bool hasImage, string imagePath, string imageFileName, string phone, string email, string title, string department)
        {
            try
            {
                string connString = ConfigurationManager.ConnectionStrings["ConnStringDB"].ConnectionString;

                using (SqlConnection con = new SqlConnection(connString))
                {

                    string query = "update ContactPerson " +
                                   "set contactPersonFName = @contactpersonFName, contactPersonLName = @contactPersonLName, contactPersonPhone = @contactPersonPhone, contactPersonMail = @contactPersonMail, contactPersonTitle = @contactPersonTitle, contactPersonDepartment = @contactPersonDepartment ";

                    if (hasImage)
                    {
                        query += ", contactPersonImageURL = @contactPersonImageURL";
                    }

                    query += " where contactPersonID = @contactPersonID";

                    SqlCommand cmd = new SqlCommand(query, con);
                    cmd.CommandType = CommandType.Text;

                    cmd.Parameters.AddWithValue("@contactPersonID", contactPersonId);

                    cmd.Parameters.AddWithValue("@contactPersonFName", fName);

                    cmd.Parameters.AddWithValue("@contactPersonLName", lName);

                    cmd.Parameters.AddWithValue("@contactPersonPhone", phone);

                    cmd.Parameters.AddWithValue("@contactPersonMail", email);

                    cmd.Parameters.AddWithValue("@contactPersonTitle", title);

                    cmd.Parameters.AddWithValue("@contactPersonDepartment", department);

                    if (hasImage)
                    {
                        cmd.Parameters.AddWithValue("@contactPersonImageURL", imagePath + "\\" + imageFileName);
                    }
                    else
                    {

                    }

                    con.Open();

                    cmd.ExecuteNonQuery();

                    con.Close();
                    con.Dispose();
                }
            }
            catch (SqlException e)
            {
                //insert logging here
                throw;
            }
            catch (Exception e)
            {
                //insert logging here
                throw;
            }
        }

        /// <summary>
        /// Get a list of contact persons, based on the name parameter.
        /// </summary>
        /// <param name="name">Specifies the name to be used.</param>
        /// <returns>Returns a list of ContactPerson objects.</returns>
        public static List<ContactPerson> GetContactPersonsByName(string name)
        {
            try
            {
                string connString = ConfigurationManager.ConnectionStrings["ConnStringDB"].ConnectionString;

                using (SqlConnection con = new SqlConnection(connString))
                {
                    List<ContactPerson> contactPersons = new List<ContactPerson>();

                    string query = "select cp.contactPersonId, cp.contactPersonFName, cp.contactPersonLName, cp.contactPersonPhone, cp.contactPersonMail, cp.contactPersonTitle, cp.contactPersonDepartment, cp.contactPersonImageURL " +
                                   "from ContactPerson cp " +
                                   "where cp.contactPersonFName like '%' + @fName + '%' or cp.contactPersonLName like '%' + @lName + '%' " +
                                   "order by cp.contactPersonFName asc";

                    SqlCommand cmd = new SqlCommand();

                    cmd.CommandText = query;
                    cmd.Connection = con;
                    cmd.CommandType = CommandType.Text;

                    cmd.Parameters.AddWithValue("@fName", name);
                    cmd.Parameters.AddWithValue("@lName", name);

                    con.Open();

                    SqlDataReader dr = cmd.ExecuteReader();

                    while (dr.Read())
                    {
                        ContactPerson cp = new ContactPerson(Convert.ToInt32(dr["contactPersonId"].ToString()));

                        cp.FirstName = dr["contactPersonFName"].ToString();
                        cp.LastName = dr["contactPersonLName"].ToString();
                        cp.Email = dr["contactPersonMail"].ToString();
                        cp.Title = dr["contactPersonTitle"].ToString();
                        cp.Department = dr["contactPersonDepartment"].ToString();
                        cp.ImageURL = dr["contactPersonImageURL"].ToString();

                        if (dr["contactPersonPhone"] != null)
                            cp.Phone = dr["contactPersonPhone"].ToString();

                        contactPersons.Add(cp);
                    }

                    con.Close();
                    con.Dispose();

                    return contactPersons;
                }
            }
            catch (SqlException e)
            {
                //insert logging here
                throw;
            }
            catch (Exception e)
            {
                //insert logging here
                throw;
            }
        }
    }
}